﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using AnyOffice.Components;
using AnyOffice.DAL;
using AnyOffice.Model;
using AnyOffice.BIZ;
using Microsoft.ApplicationBlocks.Data;

namespace AnyOffice.general.hrms.manage
{
    public partial class export : ModulePage
    {

        private string strTemp= "";

        private void LoadData()
        {
            ListItem item = new ListItem("全部", "");
            this.ddlDeptId.Items.Add(item);
            DataTable table = new DepartmentDao().SelectParentAll(base.Conn);
            if (table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    item = new ListItem("|─" + table.Rows[i]["DeptName"].ToString(), table.Rows[i]["DeptId"].ToString());
                    this.ddlDeptId.Items.Add(item);
                    this.strTemp = "";
                    this.LoadDeptTree(ref this.ddlDeptId, int.Parse(table.Rows[i]["DeptId"].ToString()), this.strTemp);
                }
            }
            this.ddlDeptId.Items.Add(new ListItem("外部/离职人员", "-1"));
        }
        private void LoadDeptTree(ref DropDownList ddlParentDept, int DeptId, string strTemp)
        {
            DataTable table = new DepartmentDao().SelectChildAll(base.Conn, DeptId);
            if (table.Rows.Count > 0)
            {
                strTemp = strTemp + "|　";
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    ListItem item = new ListItem(strTemp + "|─" + table.Rows[i]["DeptName"].ToString(), table.Rows[i]["DeptId"].ToString());
                    ddlParentDept.Items.Add(item);
                    this.LoadDeptTree(ref ddlParentDept, int.Parse(table.Rows[i]["DeptId"].ToString()), strTemp);
                }
            }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.Page.IsPostBack)
            {
                this.LoadData();
                DataTable table = new DataTable();
                table.Columns.Add("Field", Type.GetType("System.String"));
                table.Columns.Add("Name", Type.GetType("System.String"));
                table.Rows.Add(new object[] { "UserName", "姓名" });
                table.Rows.Add(new object[] { "Number", "编号" });
                table.Rows.Add(new object[] { "DeptId", "部门" });
                table.Rows.Add(new object[] { "Sex", "性别" });
                table.Rows.Add(new object[] { "Birthday", "出生日期" });
                table.Rows.Add(new object[] { "Nation", "民族" });
                table.Rows.Add(new object[] { "CardNo", "身份证号码" });
                table.Rows.Add(new object[] { "Marry", "婚姻状况" });
                table.Rows.Add(new object[] { "Politics", "政治面貌" });
                table.Rows.Add(new object[] { "NativePlace", "籍贯" });
                table.Rows.Add(new object[] { "HukouPlace", "户口所在地" });
                table.Rows.Add(new object[] { "WorkDate", "参加工作时间" });
                table.Rows.Add(new object[] { "JoinDate", "加入本单位时间" });
                table.Rows.Add(new object[] { "Education", "学历" });
                table.Rows.Add(new object[] { "Speciallity", "专业" });
                table.Rows.Add(new object[] { "School", "毕业院校" });
                table.Rows.Add(new object[] { "ContractType", "用工类型" });
                table.Rows.Add(new object[] { "Post", "职务" });
                table.Rows.Add(new object[] { "TechPost", "职称" });
                table.Rows.Add(new object[] { "HomeAddr", "家庭住址" });
                table.Rows.Add(new object[] { "HomeTel", "家庭电话" });
                table.Rows.Add(new object[] { "Email", "电子邮件" });
                table.Rows.Add(new object[] { "ContractDate", "合同期" });
                table.Rows.Add(new object[] { "LeaveType", "年休假" });
                table.Rows.Add(new object[] { "PostChange", "岗位变动情况" });
                table.Rows.Add(new object[] { "Edu", "教育背景" });
                table.Rows.Add(new object[] { "Resume", "工作简历" });
                table.Rows.Add(new object[] { "Sociaty", "社会关系" });
                table.Rows.Add(new object[] { "Reward", "奖惩记录" });
                table.Rows.Add(new object[] { "Certificate", "职务情况" });
                table.Rows.Add(new object[] { "Train", "培训记录" });
                table.Rows.Add(new object[] { "Surety", "担保记录" });
                table.Rows.Add(new object[] { "Contract", "劳动合同签订情况" });
                table.Rows.Add(new object[] { "Insure", "社保缴纳情况" });
                table.Rows.Add(new object[] { "BodyExamim", "体检记录" });
                table.Rows.Add(new object[] { "Memo", "备注" });
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    ListItem item = new ListItem(table.Rows[i]["Name"].ToString(), table.Rows[i]["Field"].ToString());
                    this.pklFields.ItemsRight.Add(item);
                }
            }
            if (this.Page.IsPostBack)
            {
                ArrayList list = new ArrayList();
                StringBuilder builder = new StringBuilder();
                builder.Append("SELECT ");
                if (this.pklFields.ItemsLeft.Count == 0)
                {
                    foreach (ListItem item2 in this.pklFields.ItemsRight)
                    {
                        builder.Append(item2.Value + ",");
                    }
                }
                else
                {
                    foreach (ListItem item3 in this.pklFields.ItemsLeft)
                    {
                        builder.Append(item3.Value + ",");
                    }
                }
                if (builder.ToString().EndsWith(","))
                {
                    builder.Remove(builder.ToString().Length - 1, 1);
                }
                builder.Replace("ContractDate", "DATENAME (year ,ContractDate1) + '-' + DATENAME(month ,ContractDate1) + '-' + DATENAME(day ,ContractDate1) + ' 至 ' + DATENAME (year ,ContractDate2) + '-' + DATENAME(month ,ContractDate2) + '-' + DATENAME(day ,ContractDate2) AS ContractDate");
                builder.Append(" FROM [dbo].[Hrms] WHERE (1=1) ");
                if (this.ddlDeptId.SelectedValue != string.Empty)
                {
                    builder.Append(" AND (DeptId = @DeptId) ");
                    list.Add(new object[] { "@DeptId", Convert.ToInt32(this.ddlDeptId.SelectedValue) });
                }
                if (this.txtNumber.Value != string.Empty)
                {
                    builder.Append(" AND (Number = @Number) ");
                    list.Add(new object[] { "@Number", this.txtNumber.Value });
                }
                if (this.selectSex.Value != string.Empty)
                {
                    builder.Append(" AND (Sex = @Sex) ");
                    list.Add(new object[] { "@Sex", Convert.ToBoolean(this.selectSex.Value) });
                }
                if (this.txtMinAge.Value != string.Empty)
                {
                    builder.Append(" AND (DATEDIFF(year,Birthday,getdate()) >= @MinAge) ");
                    list.Add(new object[] { "@MinAge", Convert.ToInt32(this.txtMinAge.Value) });
                }
                if (this.txtMaxAge.Value != string.Empty)
                {
                    builder.Append(" AND (DATEDIFF(year,Birthday,getdate()) <= @MaxAge) ");
                    list.Add(new object[] { "@MaxAge", Convert.ToInt32(this.txtMaxAge.Value) });
                }
                if (this.txtNation.Value != string.Empty)
                {
                    builder.Append(" AND (Nation = @Nation) ");
                    list.Add(new object[] { "@Nation", this.txtNation.Value });
                }
                if (this.txtCardNo.Value != string.Empty)
                {
                    builder.Append(" AND (CardNo = @CardNo) ");
                    list.Add(new object[] { "@CardNo", this.txtCardNo.Value });
                }
                if (this.selectMarry.Value != string.Empty)
                {
                    builder.Append(" AND (Marry = @Marry) ");
                    list.Add(new object[] { "@Marry", this.selectMarry.Value });
                }
                if (this.txtNativePlace.Value != string.Empty)
                {
                    builder.Append(" AND NativePlace LIKE '%' + @NativePlace + '%' ");
                    list.Add(new object[] { "@NativePlace", this.txtNativePlace.Value });
                }
                if (this.txtHukouPlace.Value != string.Empty)
                {
                    builder.Append(" AND HukouPlace LIKE '%' + @HukouPlace + '%' ");
                    list.Add(new object[] { "@HukouPlace", this.txtHukouPlace.Value });
                }
                if (this.selectEducation.Value != string.Empty)
                {
                    builder.Append(" AND (Education = @Education) ");
                    list.Add(new object[] { "@Education", this.selectEducation.Value });
                }
                if (this.txtSpeciallity.Value != string.Empty)
                {
                    builder.Append(" AND (Speciallity LIKE '%' + @Speciallity + '%') ");
                    list.Add(new object[] { "@Speciallity", this.txtSpeciallity.Value });
                }
                if (this.txtSchool.Value != string.Empty)
                {
                    builder.Append(" AND (School LIKE '%' + @School + '%') ");
                    list.Add(new object[] { "@School", this.txtSchool.Value });
                }
                if (this.txtWorkDateBegin.Value != string.Empty)
                {
                    builder.Append(" AND (WorkDate >= @WorkDateBegin) ");
                    list.Add(new object[] { "@WorkDateBegin", Convert.ToDateTime(this.txtWorkDateBegin.Value + " 00:00:00") });
                }
                if (this.txtWorkDateEnd.Value != string.Empty)
                {
                    builder.Append(" AND (WorkDate <= @WorkDateEnd) ");
                    list.Add(new object[] { "@WorkDateEnd", Convert.ToDateTime(this.txtWorkDateEnd.Value + " 23:59:59") });
                }
                if (this.txtJoinDateBegin.Value != string.Empty)
                {
                    builder.Append(" AND (JoinDate >= @JoinDateBegin) ");
                    list.Add(new object[] { "@JoinDateBegin", Convert.ToDateTime(this.txtJoinDateBegin.Value + " 00:00:00") });
                }
                if (this.txtJoinDateEnd.Value != string.Empty)
                {
                    builder.Append(" AND (JoinDate <= @JoinDateEnd) ");
                    list.Add(new object[] { "@JoinDateEnd", Convert.ToDateTime(this.txtJoinDateEnd.Value + " 23:59:59") });
                }
                if (this.txtPost.Value != string.Empty)
                {
                    builder.Append(" AND (Post LIKE '%' + @Post + '%') ");
                    list.Add(new object[] { "@Post", this.txtPost.Value });
                }
                if (this.txtTechPost.Value != string.Empty)
                {
                    builder.Append(" AND (TechPost LIKE '%' + @TechPost + '%') ");
                    list.Add(new object[] { "@TechPost", this.txtTechPost.Value });
                }
                if (this.selectPolitics.Value != string.Empty)
                {
                    builder.Append(" AND (Politics = @Politics) ");
                    list.Add(new object[] { "@Politics", this.selectPolitics.Value });
                }
                if (this.txtMemo.Value != string.Empty)
                {
                    builder.Append(" AND (Memo LIKE '%' + @Memo + '%') ");
                    list.Add(new object[] { "@Memo", this.txtMemo.Value });
                }
                builder.Append(" ORDER BY [HrmsId] ASC");
                SqlParameter[] commandParameters = new SqlParameter[list.Count];
                for (int j = 0; j < list.Count; j++)
                {
                    object[] objArray39 = (object[])list[j];
                    commandParameters[j] = new SqlParameter(objArray39[0].ToString(), objArray39[1]);
                }
                DataTable table2 = SqlHelper.ExecuteDataset(base.Conn, CommandType.Text , builder.ToString(), commandParameters).Tables[0];
                if (table2.Rows.Count == 0)
                {
                    this.pnlMain.Visible = false;
                    this.litMessage.Text = base.ShowPrompt("无符合条件的人事档案信息", "export.aspx");
                }
                else
                {
                    DataTable table3 = new DataTable();
                    table3.Columns.Add("Field", Type.GetType("System.String"));
                    table3.Columns.Add("Name", Type.GetType("System.String"));
                    table3.Rows.Add(new object[] { "UserName", "姓名" });
                    table3.Rows.Add(new object[] { "Number", "编号" });
                    table3.Rows.Add(new object[] { "DeptId", "部门" });
                    table3.Rows.Add(new object[] { "Sex", "性别" });
                    table3.Rows.Add(new object[] { "Birthday", "出生日期" });
                    table3.Rows.Add(new object[] { "Nation", "民族" });
                    table3.Rows.Add(new object[] { "CardNo", "身份证号码" });
                    table3.Rows.Add(new object[] { "Marry", "婚姻状况" });
                    table3.Rows.Add(new object[] { "Politics", "政治面貌" });
                    table3.Rows.Add(new object[] { "NativePlace", "籍贯" });
                    table3.Rows.Add(new object[] { "HukouPlace", "户口所在地" });
                    table3.Rows.Add(new object[] { "WorkDate", "参加工作时间" });
                    table3.Rows.Add(new object[] { "JoinDate", "加入本单位时间" });
                    table3.Rows.Add(new object[] { "Education", "学历" });
                    table3.Rows.Add(new object[] { "Speciallity", "专业" });
                    table3.Rows.Add(new object[] { "School", "毕业院校" });
                    table3.Rows.Add(new object[] { "ContractType", "用工类型" });
                    table3.Rows.Add(new object[] { "Post", "职务" });
                    table3.Rows.Add(new object[] { "TechPost", "职称" });
                    table3.Rows.Add(new object[] { "HomeAddr", "家庭住址" });
                    table3.Rows.Add(new object[] { "HomeTel", "家庭电话" });
                    table3.Rows.Add(new object[] { "Email", "电子邮件" });
                    table3.Rows.Add(new object[] { "ContractDate", "合同期" });
                    table3.Rows.Add(new object[] { "LeaveType", "年休假" });
                    table3.Rows.Add(new object[] { "PostChange", "岗位变动情况" });
                    table3.Rows.Add(new object[] { "Edu", "教育背景" });
                    table3.Rows.Add(new object[] { "Resume", "工作简历" });
                    table3.Rows.Add(new object[] { "Sociaty", "社会关系" });
                    table3.Rows.Add(new object[] { "Reward", "奖惩记录" });
                    table3.Rows.Add(new object[] { "Certificate", "职务情况" });
                    table3.Rows.Add(new object[] { "Train", "培训记录" });
                    table3.Rows.Add(new object[] { "Surety", "担保记录" });
                    table3.Rows.Add(new object[] { "Contract", "劳动合同签订情况" });
                    table3.Rows.Add(new object[] { "Insure", "社保缴纳情况" });
                    table3.Rows.Add(new object[] { "BodyExamim", "体检记录" });
                    table3.Rows.Add(new object[] { "Memo", "备注" });
                    StreamWriter writer = new StreamWriter(Server.MapPath("/attachment/temp/hrms_" + base.User.Identity.Name + ".xls"), false, Encoding.Default);
                    for (int k = 0; k < table2.Rows.Count; k++)
                    {
                        if (k == 0)
                        {
                            for (int n = 0; n < table2.Columns.Count; n++)
                            {
                                DataView view = new DataView(table3);
                                view.RowFilter = "Field='" + table2.Columns[n].ColumnName + "'";
                                if (view.Count > 0)
                                {
                                    writer.Write(view[0]["Name"].ToString() + "\t");
                                }
                                else
                                {
                                    writer.Write(table2.Columns[n].ColumnName + "\t");
                                }
                            }
                            writer.Write("\n");
                        }
                        for (int m = 0; m < table2.Columns.Count; m++)
                        {
                            if (table2.Rows[k][m] == DBNull.Value)
                            {
                                writer.Write("\t");
                            }
                            else if (table2.Columns[m].ColumnName == "DeptId")
                            {
                                DataTable table4 = new DepartmentDao().SelectByDeptId(base.Conn, Convert.ToInt32(table2.Rows[k]["DeptId"].ToString()));
                                if (table4.Rows.Count > 0)
                                {
                                    writer.Write(table4.Rows[0]["DeptName"].ToString() + "\t");
                                }
                                else
                                {
                                    writer.Write("\t");
                                }
                            }
                            else
                            {
                                string str = table2.Rows[k][m].GetType().ToString();
                                if (str != null)
                                {
                                    if (!(str == "System.String"))
                                    {
                                        if (str == "System.Boolean")
                                        {
                                            goto Label_1717;
                                        }
                                        if (str == "System.DateTime")
                                        {
                                            goto Label_175D;
                                        }
                                    }
                                    else
                                    {
                                        writer.Write(table2.Rows[k][m].ToString() + "\t");
                                    }
                                }
                            }
                            continue;
                        Label_1717:
                            if (Convert.ToBoolean(table2.Rows[k][m].ToString()))
                            {
                                writer.Write("男\t");
                            }
                            else
                            {
                                writer.Write("女\t");
                            }
                            continue;
                        Label_175D:
                            writer.Write(Convert.ToDateTime(table2.Rows[k][m].ToString()).ToString("d") + "\t");
                        }
                        writer.Write("\n");
                    }
                    writer.Flush();
                    writer.Close();
                    StringBuilder builder2 = new StringBuilder();
                    builder2.Append("<script>\n");
                    builder2.Append("document.getElementById(\"download_excel\").src = \"export2.aspx\";");
                    builder2.Append("</script>\n");
                    if (!this.Page.IsStartupScriptRegistered("download_excel"))
                    {
                        this.Page.RegisterStartupScript("download_excel", builder2.ToString());
                    }
                }
            }
        }
    }
}
